MySQL Group Replication 部署中遇到的错误

本文主要描述了MySQL Group Replication 部署中遇到的错误

错误一未设置白名单导致的无法启动group_replication

错误信息

2017-04-24T06:23:09.971308Z 3 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 127.0.0.1/8 to the whitelist'
2017-04-24T06:23:09.971480Z 3 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2017-04-24T06:23:09.971513Z 3 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"; group_replication_local_address: "172.17.84.71:33061"; group_replication_group_seeds: "172.17.84.71:33061,172.17.84.72:33061,172.17.84.73:33061"; group_replication_bootstrap_group: true; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"'
2017-04-24T06:23:09.972386Z 12 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2017-04-24T06:23:09.982442Z 15 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql001-relay-bin-group_replication_applier.000001' position: 4
2017-04-24T06:23:09.982441Z 3 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2017-04-24T06:23:09.982618Z 3 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2017-04-24T06:23:09.982637Z 3 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1'
2017-04-24T06:23:09.982841Z 0 [Note] Plugin group_replication reported: 'state 4257 action xa_init'
2017-04-24T06:23:09.982957Z 0 [Note] Plugin group_replication reported: 'Successfully bound to 0.0.0.0:33061 (socket=80).'
2017-04-24T06:23:09.983104Z 0 [Note] Plugin group_replication reported: 'Successfully set listen backlog to 32 (socket=80)!'
2017-04-24T06:23:09.983149Z 0 [Note] Plugin group_replication reported: 'Successfully unblocked socket (socket=80)!'
2017-04-24T06:23:09.983232Z 0 [Note] Plugin group_replication reported: 'Ready to accept incoming connections on 0.0.0.0:33061 (socket=80)!'
2017-04-24T06:23:09.983302Z 0 [Note] Plugin group_replication reported: 'connecting to 172.17.84.71 33061'
2017-04-24T06:23:09.983432Z 0 [Note] Plugin group_replication reported: 'client connected to 172.17.84.71 33061 fd 83'
2017-04-24T06:23:09.983524Z 0 [Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 172.17.84.71 refused. Address is not in the IP whitelist.'
2017-04-24T06:23:09.983620Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error connecting to the local group communication engine instance.'
2017-04-24T06:23:09.983647Z 0 [Note] Plugin group_replication reported: 'state 4257 action xa_exit'
2017-04-24T06:23:09.983926Z 0 [Note] Plugin group_replication reported: 'Exiting xcom thread'
2017-04-24T06:23:11.014814Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2017-04-24T06:24:09.991677Z 3 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2017-04-24T06:24:09.991847Z 3 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2017-04-24T06:24:09.991903Z 3 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
2017-04-24T06:24:09.992323Z 3 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2017-04-24T06:24:09.992354Z 3 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2017-04-24T06:24:09.992922Z 15 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2017-04-24T06:24:09.993527Z 12 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'

解决办法

基于网段或者IP 指定白名单，可以在线动态修改

1 2	set global group_replication_ip_whitelist = '172.17.84.71,172.17.84.72,172.17.84.73'; set global group_replication_ip_whitelist = '172.17.84.71/24';

或者添加到my.cnf中

1	loose-group_replication_ip_whitelist='172.17.84.71,172.17.84.72,172.17.84.73'

错误二无法解析主机名导致member状态一直未recoving

错误信息

通过SELECT * FROM performance_schema.replication_group_members;查询，发现MEMBER_STATE一直是recoving

2017-04-24T07:01:19.864784Z 0 [Note] Plugin group_replication reported: 'Starting group replication recovery with view_id 14930159987057432:2'
2017-04-24T07:01:19.865335Z 19 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'
2017-04-24T07:01:19.871742Z 19 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='mysql001', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2017-04-24T07:01:19.881203Z 19 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 60b61f19-289f-11e7-b97d-08002730b4d8 at mysql001 port: 3306.'
2017-04-24T07:01:19.881586Z 21 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-04-24T07:01:19.882339Z 22 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql002-relay-bin-group_replication_recovery.000001' position: 4
2017-04-24T07:01:19.883547Z 21 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master 'rpl_user@mysql001:3306' - retry-time: 60  retries: 1, Error_code: 2005
2017-04-24T07:01:19.883573Z 21 [Note] Slave I/O thread for channel 'group_replication_recovery' killed while connecting to master
2017-04-24T07:01:19.883582Z 21 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4
2017-04-24T07:01:19.883861Z 19 [ERROR] Plugin group_replication reported: 'There was an error when connecting to the donor server. Check group replication recovery's connection credentials.'
2017-04-24T07:01:19.884138Z 19 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 2/10'
2017-04-24T07:01:55.338092Z 0 [Note] Plugin group_replication reported: 'getstart group_id 4317e324'
2017-04-24T07:01:57.362663Z 0 [Note] Plugin group_replication reported: 'Marking group replication view change with view_id 14930159987057432:3'
2017-04-24T07:01:57.422909Z 0 [Note] Plugin group_replication reported: 'The member with address mysql003:3306 was declared online within the replication group'
2017-04-24T07:02:19.884808Z 19 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='mysql001', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='mysql003', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2017-04-24T07:02:19.890519Z 19 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 164b8061-28ba-11e7-9a51-080027dad0d6 at mysql003 port: 3306.'
2017-04-24T07:02:19.891514Z 26 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-04-24T07:02:19.893156Z 26 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master 'rpl_user@mysql003:3306' - retry-time: 60  retries: 1, Error_code: 2005
2017-04-24T07:02:19.893186Z 26 [Note] Slave I/O thread for channel 'group_replication_recovery' killed while connecting to master
2017-04-24T07:02:19.893194Z 26 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4
2017-04-24T0

解决办法

方法一配置hosts

1
2
3

172.17.84.71 mysql001
172.17.84.72 msyql002
172.17.84.73 mysql003

方法二

或者在配置文件my.cnf使用report_host=ip，显示指定使用IP，而非默认的主机名

错误三：mysql初始化，修改root密码，没有禁用日志，导致各节点Executed_Gtid_Set不同

错误信息

修改密码操作必须设置binlog不记录，执行后再打开，否则会引起START GROUP_REPLICATION执行报错:

1
2

[ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
[Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'

解决办法

方法一：

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set password=password('admin_123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 150
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

方法二

1	set global group_replication_allow_local_disjoint_gtids_join=ON;

方法三

如果是全新的实例，可以通过reset master清空Executed_Gtid_Set

mysql>reset master
mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000010
         Position: 1486
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10
1 row in set (0.00 sec)